create database  bbs
on
(
	name='bbs',
	filename='D:\bbs.mdf',
	size=20,
	maxsize=300,
	filegrowth=50
)
log on
(
	name='bbs_log',
	filename='D:\bbs_log.ldf',
	size=20,
	maxsize=300,
	filegrowth=50
)
go

use   bbs
go

create table bbsUsers
( 
UID int identity(1,1) ,
uName varchar(10)  null,
uSex  varchar(2) ,
uAge  int null ,
uPoint  int null 
)

create table bbsTopic
( tID  int identity(1,1) ,
  tUID int   ,
  tSID int   ,
  tTitle varchar(100) null ,
  tMsg  text null,
  tTime  datetime,
  tCount  int
)
aLter table bbsUsers add constraint PK_bbsUsers_UID  primary key([UID])
alter table bbsUsers add constraint UQ_bbsUsers_uName  unique(uName)
alter table bbsUsers add constraint CK_bbsUsers_uSex check(uSex in('男','女')) 
alter table bbsUsers add constraint CK_bbsUsers_uAge  check(uAge>=15 and uAge<=60)
alter table bbsUsers add constraint CK_bbsUsers_uPoint check(uPoint>=0)
alter table bbsTopic add constraint PK_bbsTopic_tID primary key (tID)
alter table bbsTopic add constraint FK_bbsTopic_tUID foreign key(tUID) REFERENCES bbsUsers([UID])
alter table bbsTopic add constraint FK_bbsTopic_tSID foreign key(tSID) references bbsSection([sID])



create table bbsReply
(rID  int identity(1,1)primary key,
 rUID  int references bbsUsers(UID),
  rTID  int references bbsTopic(tID),
  rMsg text null,
  rTime  datetime
)

create table bbsSection
( sID  int identity(1,1)primary key,
  sName  varchar(10) null,
  sUid   int foreign key references bbsUsers(UID)
)



insert into bbsUsers(uName,uSex,uAge,uPoint)values('小雨点','女',20,0),('逍遥','男',18,4),('七年级生','男',19,2)

  select uName ,uPoint   into bbsPoint from bbsUsers 

    
 insert into bbsSection(sName,sUid)values('技术交流','1'),(' 读书世界','3'),('生活百科','1'),('八卦区 ','3')
 insert into bbsTopic(tUID, tSID,tTitle,tMsg, tTime,tCount  )values('2',      '八卦区' ,    '范跑跑' ,               ' 谁是范跑跑'   ,          ' 2008-7-8',   '1'),('3',  '技术交流' ,  '.NET' , ' 与JAVA的区别是什么呀？' , '2008-9-1' ,  '2'),(	  '1',   '生活百科',    '今年夏天最流行什么 ',   ' 有谁知道今年夏天最流行什么呀？',  '2008-9-10',  0)
 insert into bbsReply(rID  ,rUID,rMsg  ,rTime    )values('2','2','呵呵','2020'),('1','3','呵呵呵','2020'),('3','1','呵呵呵呵','2020')
 alter table  bbsUsers drop constraint FK_bbsUsers_UID 
 alter table  bbsUsers drop constraint FK_bbsUsers_uName
 alter table  bbsTopic drop constraint FK_bbsTopic_tUID
 alter table  bbsTopic drop constraint FK_bbsTopic_tSID
 update bbsPoint set uPoint=(uPoint + 10) where uName='小雨点'
 delete from bbsReply 
 delete from bbsTopic 
 delete from bbsSection 
 delete from bbsReply 